﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using System.Data.SqlClient;


namespace Database
{
    public class DbController
    {
        private SqlCommand myCommand = new SqlCommand();
        SqlConnection myConnection;
        private static DbController instance = null;


        private DbController()
        {
        }

        public static DbController GetInstance()
        {
            if (instance == null)
            {
                instance = new DbController();
            }

            return instance;
        }


        private void OpretForbindelse()
        {

            try
            {

                string username;
                username = Environment.UserName.ToString();

                if (username == "Jakob")
                {
                    myConnection = new SqlConnection("Data Source=Jakob-Pc\\Jakob;Initial Catalog=Scooterland;Integrated Security=SSPI;Server=JAKOB-PC;Database=Scooterland;Trusted_Connection=True");
                    myConnection.Open();
                }
                else if (username == "Ole Palm")
                {
                    myConnection = new SqlConnection(//"user id=OLEPALM-THINK;" +
                        //"password=" + pw + ";" +
                                        "server=OlePalm-THINK;" +
                        // se når sql server instansen åbnes
                                        "Trusted_Connection=yes;" +
                                        "database=Scooterland; " +
                                        "connection timeout=30");
                    myConnection.Open();
                }
                else if (username == "user")
                {
                    myConnection = new SqlConnection("Data Source=WIN-9JM31EKCQ4Q\\user;Initial Catalog=Scooter;Integrated Security=SSPI;Server=WIN-9JM31EKCQ4Q\\DMUSQLSERVER;Database=Scooter;Trusted_Connection=True");
                    myConnection.Open();
                }
                // else
                // {
                //     connectionstring = "FEJL!";
                // }

            }
            catch (Exception ex)
            {
                // indsæt event om fejl som GUI kan lytte efter
            }
        }

        private void OpretSQL(string SQLsaetning)
        {
            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();

            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.ToString());
            }
            finally
            {
                myConnection.Close();
            }
        }


        public void IndsaetScootermaerke(Iscootermaerke scootermaerke)
        {
            OpretForbindelse();
            string SQL = "INSERT INTO scootermaerke VALUES ('" + scootermaerke.Maerke + "')";
            OpretSQL(SQL);
        }
        public void IndsaetUdlejningPaaOrdre(int ordrenr)
        {
            OpretForbindelse();
            string SQL = "INSERT INTO vare_ordre VALUES (" + ordrenr + ", 1)";
            OpretSQL(SQL);
        }
        public List<Iscootermaerke> HentScootermaerker()
        {
            List<Iscootermaerke> scootermaerkeListe = new List<Iscootermaerke>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM scootermaerke";
            
            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();

                
                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    string navn = myReader["maerke"].ToString();
                    DbScootermaerke scootermaerke = new DbScootermaerke(myReader["maerke"].ToString());
                    scootermaerkeListe.Add(scootermaerke);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {
                
                myConnection.Close();
            }
            return scootermaerkeListe;
        }
        public Iscooter HentScooterFraStelnummer(int stelnr)
        {
            DbScooter scooter = null;
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM scooter WHERE stelNr = " + stelnr + ";";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {

                    scooter = new DbScooter(myReader["maerke"].ToString(), Convert.ToInt32(myReader["stelNr"]), Convert.ToInt32(myReader["kmAntal"])); 
                    
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return scooter;
        }
        public void IndsaetKunde(Ikunde kunde)
        {
            OpretForbindelse();
            string SQL = "INSERT INTO kunde (navn, adresse, Postnr, tlf, mail, maerke) VALUES ('" +
                                kunde.Navn + "','" + kunde.Adresse + "','" + kunde.Postnummer + "','" + kunde.Tlf + "','" + kunde.Mail + "','" + kunde.Maerke + "')";
            OpretSQL(SQL);
        }
        public DbKunde HentSidstIndsatteKunde()
        {
            DbKunde sidsteKunde = null;
            OpretForbindelse();
            string SQL = "SELECT TOP 1 * FROM Kunde order by kundeNr desc";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQL;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    string navn = myReader["maerke"].ToString();
                    DbScootermaerke scootermaerke = new DbScootermaerke(myReader["maerke"].ToString());
                    sidsteKunde = new DbKunde(Convert.ToInt32(myReader["kundeNr"]), myReader["navn"].ToString(), myReader["adresse"].ToString(), Convert.ToInt32(myReader["postNr"]),
                                                        myReader["tlf"].ToString(), myReader["mail"].ToString(), myReader["maerke"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return sidsteKunde;
            
        }
        public List<Ikunde> HentAlleKunder()
        {
            List<Ikunde> kundeListe = new List<Ikunde>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM kunde";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbKunde kunde = new DbKunde(Convert.ToInt32(myReader["kundeNr"]), myReader["navn"].ToString(), myReader["adresse"].ToString(), Convert.ToInt32(myReader["postNr"]),
                                                        myReader["tlf"].ToString(), myReader["mail"].ToString(), myReader["maerke"].ToString());
                    kundeListe.Add(kunde);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return kundeListe;
        }


        public DbKunde hentKunde(int kundeNummer)
        {
            OpretForbindelse();
            DbKunde kunde = null;

            string SQLsaetning = "SELECT * FROM kunde WHERE kundeNr=" + kundeNummer;

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                if (myReader.Read()) 
                {
                    kunde = new DbKunde(Convert.ToInt32(myReader["kundeNr"]), myReader["navn"].ToString(), myReader["adresse"].ToString(), Convert.ToInt32(myReader["postNr"]),
                                                        myReader["tlf"].ToString(), myReader["mail"].ToString(), myReader["maerke"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return kunde;

        }

        public string HentByForPostnr(int postnummer) {
                
            string byNavn = "";
            OpretForbindelse();
            string SQLsaetning = "select * from postBy WHERE postNr=" + postnummer;

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();

                if (myReader.Read())
                {
                    byNavn = myReader["bynavn"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return byNavn;
        }

        public void IndsaetKundescooter(Iscooter scooter)
        {
            OpretForbindelse();
            string SQL = "INSERT INTO scooter (stelNr, maerke, kundeNr, scooterType) VALUES ('" + scooter.Stelnummer + "','" + scooter.Maerke + "','" + scooter.KundeNr + "','KundeScooter')";
            OpretSQL(SQL);
        }
        public List<Iopgave> HentAlleOpgaver()
        {
            List<Iopgave> opgaveListe = new List<Iopgave>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM opgave";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbOpgave opgave = new DbOpgave(Convert.ToInt32(myReader["opgaveNr"]), myReader["navn"].ToString(), myReader["beskrivelse"].ToString());
                    opgaveListe.Add(opgave);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return opgaveListe;
        }
       
        public List<Iansat> HentAlleMekanikere()
        {
            List<Iansat> mekanikerListe = new List<Iansat>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM ansat INNER JOIN ansatType ON (medarbejdertype = ansatType.typeId) WHERE ansatType.ansatType = 'mekaniker' ;";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbMekaniker mekaniker = new DbMekaniker(Convert.ToInt32(myReader["medarbejderNr"]), myReader["navn"].ToString());
                    mekanikerListe.Add(mekaniker);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Fejl :", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return mekanikerListe;
        }

        public bool MekanikerMaerkeMatch(string speciale, int medarbejderNr)
        {
            bool resultat = false;
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM ansat INNER JOIN ansatMaerke ON (ansat.medarbejderNr = ansatMaerke.ansat) INNER JOIN scootermaerke ON (ansatMaerke.maerke = scootermaerke.maerke) WHERE scootermaerke.maerke = '" + speciale + "' AND dbo.ansat.medarbejderNr = " + medarbejderNr + ";";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();

                if (myReader.Read())
                {
                    resultat = true;
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return resultat;
        }

        
        public List<Iordre> HentAlleUafsluttedeOrdrerDenAnsatteErBooketTil(int medarbejderNr)
        {
            
            List<Iordre> ordreListe = new List<Iordre>();
            OpretForbindelse();
            string SQLsaetning = "select DISTINCT o.ordreNr, o.ordreDato, o.kundeNr from (ordre o JOIN opgave_booking_ordre obo ON o.ordreNr=obo.ordreNr JOIN booking b ON b.bookingNr=obo.bookingNr JOIN opgave opg ON opg.opgaveNr=obo.opgaveNr) WHERE slutDato IS NULL AND medarbejderNr =" + medarbejderNr;

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DateTime slutDato = DateTime.MinValue;
                    DbOrdre ordre = new DbOrdre(Convert.ToInt32(myReader["ordreNr"]), Convert.ToDateTime(myReader["ordreDato"]), Convert.ToInt32(myReader["kundeNr"]), slutDato);
                    ordreListe.Add(ordre);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Fejl :", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return ordreListe;
        
        }

        public List<Iopgave> HentMedarbejderensOpgaverForOrdre(int medarbejderNr, int ordreNr)
        {
            List<Iopgave> opgaveListe = new List<Iopgave>();
            OpretForbindelse();
            string SQLsaetning = "select opg.opgaveNr, opg.navn, opg.beskrivelse  from (ordre o JOIN opgave_booking_ordre obo ON o.ordreNr=obo.ordreNr JOIN booking b ON b.bookingNr=obo.bookingNr JOIN opgave opg ON opg.opgaveNr=obo.opgaveNr) WHERE o.slutDato IS NULL AND o.ordreNr= " + ordreNr + " AND medarbejderNr= " + medarbejderNr;

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbOpgave opgave = new DbOpgave(Convert.ToInt32(myReader["opgaveNr"]), myReader["navn"].ToString(), myReader["beskrivelse"].ToString());
                    opgaveListe.Add(opgave);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Fejl :", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return opgaveListe;
        }

        public List<Iordre> HentAlleOrdrer()
        {
            List<Iordre> ordreListe = new List<Iordre>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM ordre";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {

                    if (myReader["slutDato"] == DBNull.Value)
                    {
                        DateTime slutdato = DateTime.MinValue; 
                        DbOrdre ordre = new DbOrdre(Convert.ToInt32(myReader["ordreNr"]), Convert.ToDateTime(myReader["ordreDato"]), Convert.ToInt32(myReader["kundeNr"]), slutdato);
                        ordreListe.Add(ordre);
                    }
                    else
                    {
                        DbOrdre ordre = new DbOrdre(Convert.ToInt32(myReader["ordreNr"]), Convert.ToDateTime(myReader["ordreDato"]), Convert.ToInt32(myReader["kundeNr"]), Convert.ToDateTime(myReader["slutDato"]));
                        ordreListe.Add(ordre);
                    }
                }

                
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return ordreListe;
        }

        public void OpretOrdre(Iordre ordre)
        {
            OpretForbindelse();
            string SQL = "INSERT INTO ordre (ordreDato, kundeNr) VALUES ('" + ordre.Ordredato.ToString("yyyy-MM-dd HH:mm:ss") + "'," + ordre.Kundenummer + ");";
            OpretSQL(SQL);
        }
        public void OpretOrdreVareLinie(int ordrenr, int varenr)
        {
            OpretForbindelse();
            string sql = "INSERT INTO vare_ordre (ordreNr, vareNr) VALUE (" + ordrenr + ", " + varenr + ")";
            OpretSQL(sql);
        }
        public void OpretBooking(Ibooking booking)
        {
            OpretForbindelse();
            string SQL = "INSERT INTO booking (tidspunkt, medarbejderNr) VALUES ('" + booking.Tidspunkt.ToString("yyyy-MM-dd HH:mm:ss") + "'," + booking.Medarbejdernummer + ");";
            OpretSQL(SQL);
        }
        public void OpretBookingOpgaveOrdreLinie(int opgavenr, int bookingnr, int ordrenr)
        {
            OpretForbindelse();
            string SQL = "INSERT INTO opgave_booking_ordre (opgaveNr, bookingNr, ordreNr) VALUES (" + opgavenr + "," + bookingnr + "," + ordrenr + ");";
            OpretSQL(SQL);
        }
        public int HentSidstIndsatteOrdrenr()
        {
            OpretForbindelse();
            string SQL = "SELECT TOP 1 * FROM ordre order by ordreNr desc";
            int ordrenr = 0;
            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQL;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    ordrenr = Convert.ToInt32(myReader["ordreNr"]);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return ordrenr;
        }
        public int HentVareUdfraKm(int km)
        {
            OpretForbindelse();
            string SQL = "SELECT  * FROM vare WHERE navn = '" + km + " km'";
            int varenr = 0;
            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQL;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    varenr = Convert.ToInt32(myReader["vareNr"]);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return varenr;
        }
        public List<Iordre> HentUafsluttedeOrdrer()
        {
            List<Iordre> ordreListe = new List<Iordre>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM ordre WHERE slutDato IS NULL";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                        DateTime slutdato = DateTime.MinValue;
                        DbOrdre ordre = new DbOrdre(Convert.ToInt32(myReader["ordreNr"]), Convert.ToDateTime(myReader["ordreDato"]), Convert.ToInt32(myReader["kundeNr"]), slutdato);
                        ordreListe.Add(ordre);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return ordreListe;
        }
        public List<Iordre> HentOrdrerDESC()
        {
            List<Iordre> ordreListe = new List<Iordre>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM ordre ORDER BY ordreNr DESC";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DateTime slutdato = DateTime.MinValue;
                    DbOrdre ordre = new DbOrdre(Convert.ToInt32(myReader["ordreNr"]), Convert.ToDateTime(myReader["ordreDato"]), Convert.ToInt32(myReader["kundeNr"]), slutdato);
                    ordreListe.Add(ordre);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return ordreListe;
        }
        public List<Iordre> HentUafsluttedeOrdrerForSpecifikKunde(int kundenummer)
        {
            List<Iordre> ordreListe = new List<Iordre>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM ordre WHERE kundeNr = " + kundenummer + " AND slutDato IS NULL";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DateTime slutdato = DateTime.MinValue;
                    DbOrdre ordre = new DbOrdre(Convert.ToInt32(myReader["ordreNr"]), Convert.ToDateTime(myReader["ordreDato"]), Convert.ToInt32(myReader["kundeNr"]), slutdato);
                    ordreListe.Add(ordre);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return ordreListe;
        }

        public int HentSidstIndsatteBookingnr()
        {
            OpretForbindelse();
            string SQL = "SELECT TOP 1 * FROM booking order by bookingNr desc";
            int bookingnr = 0;
            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQL;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    bookingnr = Convert.ToInt32(myReader["bookingNr"]);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return bookingnr;
        }

        public DbKunde HentTelefonnummer(Iordre valgtOrdre)
        {
         OpretForbindelse();
            string SQL = "SELECT * FROM kunde WHERE kundeNr=" + valgtOrdre.Kundenummer;
            DbKunde kunde = null;  
            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQL;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    kunde = new DbKunde (Convert.ToInt32(myReader["kundeNr"]), myReader["navn"].ToString(), myReader["adresse"].ToString(), Convert.ToInt32(myReader["postNr"]),
                                                        myReader["tlf"].ToString(), myReader["mail"].ToString(), myReader["maerke"].ToString());
                }

            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return kunde;
        }

        public void FaerdigmeldOrdre(DateTime slutdato, Iordre valgtOrdre)
        {
            OpretForbindelse();
            string SQL = "UPDATE ordre SET slutDato = '" + slutdato.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE ordreNr =" + valgtOrdre.Ordrenummer + ";";
            OpretSQL(SQL);
        }

        public List<Iscooter> HentAlleLedigeUdlejningsscootere()
        {
            List<Iscooter> scooterliste = new List<Iscooter>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM scooter WHERE scooterType = 'Udlejningsscooter' AND udlejet = 0";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbScooter scooter = new DbScooter(myReader["maerke"].ToString(), Convert.ToInt32(myReader["stelNr"]), Convert.ToInt32(myReader["kmAntal"]));
                    scooterliste.Add(scooter);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return scooterliste;
        }
        public List<Iscooter> HentAlleUdlejningsscootere()
        {
            List<Iscooter> scooterliste = new List<Iscooter>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM scooter WHERE scooterType = 'Udlejningsscooter'";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbScooter scooter = new DbScooter(myReader["maerke"].ToString(), Convert.ToInt32(myReader["stelNr"]), Convert.ToInt32(myReader["kmAntal"]));
                    scooterliste.Add(scooter);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return scooterliste;
        }
        public void OpdaterKmAntal(int stelnr, int kmAntal)
        {
            OpretForbindelse();
            string SQL = "UPDATE scooter SET kmAntal = " + kmAntal + " WHERE stelNr = " + stelnr + ";";
            OpretSQL(SQL);
        }
        public List<Iordre> HentAfsluttedeUfaktureredeOrdrer()
        {
            List<Iordre> ordreListe = new List<Iordre>();
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM ordre WHERE slutDato IS NOT NULL AND ordreNr NOT IN (select ordreNr from regning)";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbOrdre ordre = new DbOrdre(Convert.ToInt32(myReader["ordreNr"]), Convert.ToDateTime(myReader["ordreDato"]), Convert.ToInt32(myReader["kundeNr"]), Convert.ToDateTime(myReader["slutDato"]));
                    ordreListe.Add(ordre);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return ordreListe;
        }

        public List<Ivare> HentAlleVarerTilOrdre(Iordre aktuelordre)
        {

            List<Ivare> vareliste = new List<Ivare>();
            int ordrenummer = aktuelordre.Ordrenummer;

            OpretForbindelse();
            string SQLsaetning = "select v.vareNr, v.navn, v.beskrivelse, v.pris FROM (vare v JOIN vare_ordre vo ON v.vareNr=vo.vareNr) WHERE vo.ordreNr = " + ordrenummer;

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbVare vare = new DbVare(Convert.ToInt32(myReader["vareNr"]), myReader["navn"].ToString(), myReader["beskrivelse"].ToString(), Convert.ToInt32(myReader["pris"]));
                    vareliste.Add(vare);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return vareliste;
        }

        public List<Ivare> HentAlleVarer()
        {

            List<Ivare> vareliste = new List<Ivare>();

            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM vare";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();

                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbVare vare = new DbVare(Convert.ToInt32(myReader["vareNr"]), myReader["navn"].ToString(), myReader["beskrivelse"].ToString(), Convert.ToInt32(myReader["pris"]));
                    vareliste.Add(vare);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return vareliste;
        }

        public int TilfoejVareTilOrdre(int ordreNr, int vareNummer)
        {
            OpretForbindelse();
            int id = 0;
            string insertSQL = "INSERT INTO vare_ordre (ordreNr,vareNr) VALUES (" + ordreNr +","+ vareNummer +")";
            
            try
            {
                myCommand.Connection = myConnection;
                myCommand.CommandText = insertSQL;
                myCommand.ExecuteNonQuery();

                myCommand.CommandText = "SELECT @@IDENTITY";
                id = Convert.ToInt32(myCommand.ExecuteScalar()); 
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {
                myConnection.Close();
            }
            return id;
        }

        public void sletVareFraOrdre(int id)
        {
            OpretForbindelse();
  
            string sql = "DELETE FROM vare_ordre WHERE id =" + id;

            try
            {
                myCommand.Connection = myConnection;
                myCommand.CommandText = sql;
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {
                myConnection.Close();
            }
        }

        public List<Ivare> HentVarerTildeltOrdreOpgave(int ordreNummer, int opgaveNummer)
        {

            List<Ivare> vareliste = new List<Ivare>();

            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM vare";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();

                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbVare vare = new DbVare(Convert.ToInt32(myReader["vareNr"]), myReader["navn"].ToString(), myReader["beskrivelse"].ToString(), Convert.ToInt32(myReader["pris"]));
                    vareliste.Add(vare);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {
                myConnection.Close();
            }
            return vareliste;
        }
        public bool FindesKmVare(int kilometerantal)
        {
            bool resultat = false;
            OpretForbindelse();
            string SQLsaetning = "SELECT * FROM vare WHERE navn = '" + kilometerantal + " km'";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();

                if (myReader.Read())
                {
                    resultat = true;
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return resultat;
        }
        public void OpretKmVare(int km, int pris)
        {
            OpretForbindelse();
            string sql = "INSERT INTO vare (navn, beskrivelse, pris) VALUES ('" + km + " km', '" + km + " km', " + pris + ")";
            OpretSQL(sql);
        }
        public int HentSidstIndsatteVare()
        {
            OpretForbindelse();
            string SQL = "SELECT TOP 1 * FROM vare order by vareNr desc";
            int varenr = 0;
            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQL;
                myReader = myCommand.ExecuteReader();


                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    varenr = Convert.ToInt32(myReader["vareNr"]);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {

                myConnection.Close();
            }
            return varenr;
        }

        public void ScooterRetur(int stelnummmer)
        {
            OpretForbindelse();
            string sql = "UPDATE scooter SET udlejet = 0 WHERE stelNr = " + stelnummmer;
            OpretSQL(sql);
        }

        public List<Ikunde> HentKunderDerIkkeHarHarHaftOrdreIAntalDage(int antalDage)
        {

            List<Ikunde> kundeListe = new List<Ikunde>();

            OpretForbindelse();
            string SQLsaetning = "SELECT k.kundeNr, k.navn, k.adresse, k.postNr, k.tlf, k.mail, k.maerke from (kunde k JOIN ordre o ON k.kundeNr=o.kundeNr) where ordreDato < (GETDATE()-"+ antalDage +")";
                //"SELECT * from ordre where ordreDato < (GETDATE()-" + antalDage + ")";

            try
            {
                SqlDataReader myReader = null;
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLsaetning;
                myReader = myCommand.ExecuteReader();

                while (myReader.Read()) // sålænge der er tupler i resultatet
                {
                    DbKunde kunde = new DbKunde(Convert.ToInt32(myReader["kundeNr"]), myReader["navn"].ToString(), myReader["adresse"].ToString(), Convert.ToInt32(myReader["postNr"]),
                                                        myReader["tlf"].ToString(), myReader["mail"].ToString(), myReader["maerke"].ToString());
                    kundeListe.Add(kunde);
                }
            }
            catch (Exception ex)
            {
                throw new NullReferenceException("ku", ex);
            }
            finally
            {
                myConnection.Close();
            }
            return kundeListe;
        }

        public void OpretVare(string navn, string beskrivelse, int pris)
        {



            OpretForbindelse();
            string SQL = "INSERT INTO vare (navn,beskrivelse,pris) VALUES ('" + navn + "','" + beskrivelse + "'," + pris + ")";
            OpretSQL(SQL);

        }
    }
}
